package com.chuangyiruanke.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by zxh on 2017/2/14.
 */
public class ExcelUtil {

    public static Workbook getWorkbook(File file, String fileName) throws java.io.IOException {
        InputStream stream = new FileInputStream(file);
        return getWorkbook(stream, FileUtil.getExtension(fileName));
    }

    public static Workbook getWorkbook(InputStream stream, String extension) throws IOException {
        if ("xlsx".equals(extension)) {
            return new XSSFWorkbook(stream);
        } else if ("xls".equals(extension)) {
            return new HSSFWorkbook(stream);
        } else {
            throw new IllegalArgumentException("未知的工作表类型");
        }
    }

    public static Workbook getWorkbook(File file) throws java.io.IOException {
        return getWorkbook(file, file.getName());
    }

    public static Workbook getWorkbook(String fileName) throws java.io.IOException {
        File file = new File(fileName);
        return getWorkbook(file);
    }

    public static String getCellValue(Cell cell, String format) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                return Boolean.toString(cell.getBooleanCellValue());
            case ERROR:
                return Byte.toString(cell.getErrorCellValue());
            case FORMULA:
                Workbook wb = cell.getSheet().getWorkbook();
                CreationHelper crateHelper = wb.getCreationHelper();
                FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                return getCellValue(evaluator.evaluateInCell(cell), format);
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    if (StringUtils.isNullOrEmpty(format)) {
                        return Double.toString(value);
                    }
                    return String.format(Locale.CHINESE, format, value);
                }
            case BLANK:
            default:
                return "";
        }
    }

    public static Map<Integer, String> getColumnIndexMap(Sheet sheet, int rowNo) {
        Row row = sheet.getRow(rowNo);
        return getColumnIndexMap(row);
    }

    public static Map<Integer, String> getColumnIndexMap(Row row) {
        Map<Integer, String> map = new HashMap<>();
        if(row == null){
            return map;
        }
        Iterator<Cell> iterator = row.cellIterator();
        while (iterator.hasNext()) {
            Cell cell = iterator.next();
            String value = getCellValue(cell, null);
            map.put(cell.getColumnIndex(), value);
        }
        return map;
    }

    public static Map<String, Integer> getColumnNameMap(Sheet sheet, int rowNo) {
        Row row = sheet.getRow(rowNo);
        return getColumnNameMap(row);
    }

    public static Map<String, Integer> getColumnNameMap(Row row) {
        Map<String, Integer> map = new HashMap<>();
        if(row == null){
            return map;
        }
        Iterator<Cell> iterator = row.cellIterator();
        while (iterator.hasNext()) {
            Cell cell = iterator.next();
            String value = getCellValue(cell, null);
            if (map.containsKey(value)){
                map.replace(value, cell.getColumnIndex());
            } else {
                map.put(value, cell.getColumnIndex());
            }
        }
        return map;
    }
}
